import numpy as np
import pandas as pd
import pymysql
import pymysql.cursors
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class Mysqlutils(object):
    def __init__(self):
        self.conn=pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3307,
            charset='utf8'
        )
    
    def is_holiday(self,data):
        if data in['2004-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04','2024-10-05','2024-10-06','2025-10-07','2025-01-01','2024-01-02','2025-01-03']:
            return 1
        return 0
      
    def get_scenic_data (self): 
        
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql =""" 
        select DATE(g.create_time) as date,HOUR(g.create_time) as hour ,count(*) as count 
        FROM order_user_gate_rel  g 
        WHERE HOUR(g.create_time) BETWEEN 6 AND 23 group by date,hour
        """
            
        cursor.execute(sql)
        ret=cursor.fetchall()
        df=pd.DataFrame(ret)
        #print(df.head)
        
        date_range=pd.date_range(start='2024-07-01',end='2024-12-31',freq='D')
        hours=range(6,24)
        full_index=pd.MultiIndex.from_product([date_range,hours],names=['date','hour'])
        df_full=df.set_index(['date','hour']).reindex(full_index,fill_value=0).reset_index()
        
        df_pivot=df_full.pivot(index='date',columns='hour',values='count')
        #print(df_pivot.head)
        
        df_pivot['dow']=df_pivot.index.dayofweek
        df_pivot['month']=df_pivot.index.month
        df_pivot['is_holiday']=df_pivot.index.map(self.is_holiday)
        #print(df_pivot.head)
        df_pivot=pd.get_dummies(df_pivot,columns=['dow','month'],dtype=int)
        #print(df_pivot.head)
        hours_colums=list(range(6,24))
        df_hours=df_pivot[hours_colums].copy()
        
        feature_colums=[col for col in df_pivot.columns if col not in hours_colums]
        df_feature=df_pivot[feature_colums].copy()
        
        scaler=MinMaxScaler()
        scaled_hours=scaler.fit_transform(df_hours)
        dump(scaler,"NN/scaler.joblib")
        
        df_hours_scaled=pd.DataFrame(scaled_hours,columns=hours_colums,index=df_hours.index)
        
        df_pivot_clean=pd.concat([df_hours_scaled,df_feature],axis=1)
        print(df_pivot_clean.head)
        df_pivot_clean.to_csv('NN/scenic_data.csv',index=False)
                

if __name__=='__main__':
    mu=Mysqlutils()
    mu.get_scenic_data()